SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE  PROCEDURE [dbo].[usp_SearchRequireByState]
      (
        @JOL_Require_Company VARCHAR(50) ,
        @JOL_Require_VerifyStatus VARCHAR(50) 
      )
AS 
      SELECT    b.JOL_Employer_Name ,
                a.JOL_Require_ID ,
                a.JOL_Require_Company ,
                a.JOL_Require_Job ,
                a.JOL_Require_VerifyStatus ,
                a.JOL_Require_ClickNumber ,
                a.JOL_Require_AddTime ,
                b.JOL_Employer_Email ,
                a.JOL_Require_Study_Level ,
                a.JOL_Require_Profession ,
                a.OnlyForSysu ,
                a.JOL_Require_Number ,
                CASE a.JOL_Require_Student_Type
                  WHEN 1 THEN '应届生'
                  WHEN 2 THEN '实习生'
                END
      FROM      dbo.JOL_Require AS a
      INNER JOIN dbo.JOL_Employer AS b
      ON        a.JOL_Require_Company = b.JOL_Employer_Account
      WHERE     ( b.JOL_Employer_Name LIKE '%' + @JOL_Require_Company + '%' )
                AND ( a.JOL_Require_VerifyStatus LIKE '%' + @JOL_Require_VerifyStatus + '%' )
      ORDER BY  a.JOL_Require_AddTime DESC						






GO
